unit dbmodule;

interface

uses
  SysUtils, Classes, DB, DBTables, ADODB, Dialogs, IniFiles, includefile, StrUtils;

type
  Tdbmodulefrm = class(TDataModule)
    DBCNCT: TADOConnection;
    dbquery: TADOQuery;
    dbtmpqry: TADOQuery;
    T_RCD: TADOTable;
    T_EMPLOYEE: TADOTable;
    dbtmpqryEx: TADOQuery;
    TSys_User: TADOTable;
    T_PRODUCT: TADOTable;
    procedure DataModuleCreate(Sender: TObject);
    procedure DataModuleDestroy(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    procedure connectdb(const dbfile:string;const paswd:string='');
    procedure unconnectdb;
    function usrlogin(const usr_name,usr_pswd:string;var usr_pwr:string):integer;
    procedure getcontractorlist(mstr:TStrings);
    procedure getRFIDName(mstr:TStrings);
    procedure getScaleID(mstr:TStrings);
    procedure getProducts(mstr:TStrings);
    procedure replaceRFID(const tmpRFID,oldRFID:string);
    procedure getRcdRfidByDate(mstr:TStrings;dt:TdateTime);
    procedure getRFIDListByConstractor(ContrctID:Integer;mstr:TStrings);
    function getConstractorNameByID(ID:Integer):string;
    function addConstractor(cname,lrfid,urfid,remark:string):integer;
    procedure alterPickerConstractor(rfid:String;ContractorID:integer);
    procedure UpdateConstractor(cname,lrfid,urfid,remark:string;ID:integer); 
    function getPicker(rfid:string):TPicker;
    function tmpRfidExist(tmprfid:String):Boolean;
    procedure dealtmpRfid;
    
  end;

var
  dbmodulefrm: Tdbmodulefrm;

implementation

{$R *.dfm}

procedure Tdbmodulefrm.dealtmpRfid;
var
  tmpRfidArr:Array of TtmpRfid;
  tmpRfidCount,i:integer;
begin
  i:=0;
  //get tmp Rfid info
  with dbtmpqry do begin
    active:=False;
    sql.clear;
    sql.Add('SELECT * FROM TEMPRFID');
    sql.Add(FORMAT('WHERE TYPE=%d',[1]));
    execsql;
    active:=True;
    tmpRfidCount:=RecordCount;
    if tmpRfidCount<=0 then exit;
    setLength(tmpRfidArr,tmpRfidCount);
    First;
    while not eof do begin
      tmpRfidArr[i].ID:=FieldByName('ID').AsInteger;
      tmpRfidArr[i].newRfid:=FieldByName('NEWRFID').AsString;
      tmpRfidArr[i].oldRfid:=FieldByName('OLDRFID').AsString;
      tmpRfidArr[i].typTpRfid:=FieldByName('TYPE').AsInteger;
      tmpRfidArr[i].sttm:=FieldByName('STRATTIME').AsDateTime;
      tmpRfidArr[i].endtm:=FieldByName('ENDTIME').AsDateTime;
      Next;
      inc(i);
    end;
    Active:=False;
  end;
  //UpDate Records and set temp rfid's records to original.
  for i:=0 to tmpRfidCount-1 do begin
    if tmpRfidArr[i].endtm<date then tmpRfidArr[i].typTpRfid:=2;
    with dbtmpqry do begin
      active:=False;
      sql.clear;
      sql.Add('UpDate RECORD');
      sql.Add(Format('SET RFID=''%s''',[RightStr('0000000000'+tmpRfidArr[i].newRfid,10)]));
      sql.Add(Format('WHERE RFID=''%s''',[tmpRfidArr[i].oldRfid]));
      sql.Add(Format('AND DATETIME>=#%s#',[FormatDateTime('YYYY-MM-DD',tmpRfidArr[i].sttm)]));
      SQL.Add(Format('AND DATETIME<#%s#',[FormatDateTime('YYYY-MM-DD',tmpRfidArr[i].endtm+1)]));
      execsql;
    end;
  end;
  exsleep(100);
  //UpDate tmprfid
  for i:=0 to tmpRfidCount-1 do begin
    with dbtmpqry do begin
      active:=False;
      sql.clear;
      sql.Add('UpDate TEMPRFID');
      sql.Add(Format('SET TYPE=%d',[tmpRfidArr[i].typTpRfid]));
      sql.Add(Format('WHERE ID=%d',[tmpRfidArr[i].ID]));
      execsql;
    end;
  end;
  exsleep(100);
  dbtmpqry.Active:=False;
end;

function Tdbmodulefrm.tmpRfidExist(tmprfid:String):Boolean;
begin
  Result:=False;
  with dbtmpqry do begin
    active:=False;
    sql.clear;
    sql.Add('SELECT * FROM RECORD');
    sql.Add(Format('Where Right(RFID,%d)=''%s''',[RFIDLEN,tmprfid]));
    execsql;
    Active:=True;
    if RecordCount>0 then
      Result:=True;
    Active:=False;
  end;
end;

function Tdbmodulefrm.getPicker(rfid:string):TPicker;
var
  tmp:TPicker;
begin
  with dbtmpqryEx do begin
    active:=False;
    sql.clear;
    sql.Add('SELECT * FROM EMPLOYEE');
    sql.Add(Format(' Where Right(RFID,%d)=''%s''',[RFIDLEN,rfid]));
    execsql;
    active:=True;
    tmp.rfid:=FieldByName('RFID').AsString;
    tmp.name:=FieldByName('NAME').AsString;
    tmp.sex:=FieldByName('SEX').AsString;
    tmp.birthday:=FieldByName('BIRTHDAY').AsDateTime;
    tmp.TFN:=FieldByName('TFN').AsString;
    tmp.TFNtype:=Byte(FieldByName('TFNTYPE').AsInteger);
    tmp.contractor:=FieldByName('CONTRACTORNAME').AsInteger;
    tmp.ID_DOC:=FieldByName('ID_DOC').AsString;
    tmp.MOB_TEL:=FieldByName('MOB_TEL').AsString;
    tmp.address:=FieldByName('ADDRESS').AsString;
    tmp.Email:=FieldByName('EMAIL').AsString;
    tmp.Ext:=FieldByName('EXT').AsString;
    tmp.HM_TEL:=FieldByName('HM_TEL').AsString;
    Active:=False;
  end;
  Result:=tmp;
end;

procedure Tdbmodulefrm.UpdateConstractor(cname,lrfid,urfid,remark:string;ID:integer);
begin
  with dbtmpqryEx do begin
    active:=False;
    sql.clear;
    sql.Add('UpDate CONTRACTOR ');
    sql.Add(Format('Set [NAME]=''%s'', LRFID=''%s'',URFID=''%s'',REMARK=''%s''',
                [cname,lrfid,urfid,remark]));
    sql.Add(Format('Where ID=%d',[ID]));
    execsql;
    active:=False;
  end;
end;

procedure Tdbmodulefrm.alterPickerConstractor(rfid:String;ContractorID:integer);
begin
  with dbtmpqry do begin
    active :=False;
    sql.Clear;
    sql.Add('Update EMPLOYEE');
    sql.Add(format(' Set CONTRACTORNAME=%d',[ContractorID]));
    sql.Add(format(' Where Right(RFID,%d)=''%s''',[RFIDLEN,rfid]));
    execsql;  
  end;
end;

function Tdbmodulefrm.addConstractor(cname,lrfid,urfid,remark:string):integer;
begin
  with dbtmpqryEx do begin
    active:=False;
    sql.clear;
    sql.Add('INSERT INTO CONTRACTOR ');
    sql.add('([NAME],LRFID,URFID,REMARK) VALUES');
    sql.add(Format(' (''%s'',''%s'',''%s'',''%s'')',
              [cname,lrfid,urfid,remark]));
    execsql;
    sql.clear;
    sql.Add('Select Max(ID) As NewID From CONTRACTOR');
    execsql;
    Active:=True;
    First;
    Result:=FieldByName('NewID').AsInteger; 
    Active:=False;
  end;
end;

function Tdbmodulefrm.getConstractorNameByID(ID:Integer):string;
begin
  with dbtmpqryEx do begin
    active :=False;
    sql.Clear;
    sql.Add(Format('SELECT [NAME] FROM CONTRACTOR WHERE ID=%d',[ID]));
    execsql;
    Active:=True;
    First;
    Result:=Format('%-4d%s',[ID,FieldByName('NAME').AsString]);
    Active:=False;
  end;
end;

procedure Tdbmodulefrm.getRFIDListByConstractor(ContrctID:Integer;mstr:TStrings);
var
  list_tmp:Tstringlist;
begin
  list_tmp:=Tstringlist.Create;
  list_tmp.Clear;
  with dbtmpqry do begin
    active :=False;
    sql.Clear;
    sql.Add(Format('Select Right(RFID,%d) as RFID,[NAME] from EMPLOYEE',[RFIDLen]));
    sql.Add(Format('where CONTRACTORNAME=%d',[ContrctID]));
    sql.Add(Format('ORDER BY Right(RFID,%d)',[RFIDLen]));
    execsql;
    active:=True;
    first;
    while not eof do begin
      list_tmp.Add(Format('%-10s%s',[FieldByName('RFID').AsString,
                              FieldByName('NAME').AsString]));
      next;
    end;
    Active:=False;
  end;
  mstr.Assign(list_tmp);
  list_tmp.Free;
end;

procedure Tdbmodulefrm.replaceRFID(const tmpRFID,oldRFID:string);
begin
  with dbtmpqry do begin
    active :=False;
    sql.Clear;
    sql.Add('Update RECORD');
    sql.Add(format(' Set RFID=''%s''',[RightStr('0000000000'+tmpRFID,RFIDLEN)]));
    sql.Add(format(' Where Right(RFID,%d)=''%s''',[RFIDLEN,oldRFID]));
    execsql;
    active :=False;
    sql.Clear;
    sql.Add('Update EMPLOYEE');
    sql.Add(format(' Set RFID=''%s''',[RightStr('0000000000'+tmpRFID,RFIDLEN)]));
    sql.Add(format(' Where Right(RFID,%d)=''%s''',[RFIDLEN,oldRFID]));
    execsql;
    active :=False;
    sql.Clear;
    sql.Add('INSERT INTO TEMPRFID');
    sql.Add('(NEWRFID,OLDRFID,TYPE) VALUES');
    sql.Add(format('(''%s'',''%s'',%d)',
                  [RightStr('0000000000'+tmpRFID,10),
                   RightStr('0000000000'+OLDRFID,10),
                   0]));
    execsql;
  end;
end;

procedure Tdbmodulefrm.getRcdRfidByDate(mstr:TStrings;dt:TDateTime);
var
  list_tmp:Tstringlist;
begin
  list_tmp:=Tstringlist.Create;
  list_tmp.Clear;
  with dbtmpqry do begin
    active :=False;
    sql.Clear;
    sql.Add(Format('SELECT DISTINCT Right(RFID,%d) AS RFID FROM RECORD',[RFIDLen]));
    SQL.Add(Format('WHERE DATETIME>=#%s#',[FormatDateTime('YYYY-MM-DD',dt)]));
    SQL.Add(Format('AND DATETIME<#%s#',[FormatDateTime('YYYY-MM-DD',dt+1)]));
    execsql;
    active:=True;
    first;
    while not eof do begin
      list_tmp.Add(FieldByName('RFID').AsString);
      next;
    end;
    Active:=False;
  end;
  mstr.Assign(list_tmp);
  list_tmp.Free;
end;

procedure  Tdbmodulefrm.getRFIDName(mstr:TStrings);
var
  list_tmp:Tstringlist;
begin
  list_tmp:=Tstringlist.Create;
  list_tmp.Clear;
  with dbtmpqry do begin
    active :=False;
    sql.Clear;
    sql.Add(Format('Select Right(RFID,%d) as RFID,[NAME] from EMPLOYEE order by Right(RFID,%d)',[RFIDLen,RFIDLen]));
    execsql;
    active:=True;
    first;
    while not eof do begin
      list_tmp.Add(Format('%-10s%s',[FieldByName('RFID').AsString,
                              FieldByName('NAME').AsString]));
      next;
    end;
    Active:=False;
  end;
  mstr.Assign(list_tmp);
  list_tmp.Free;
end;
//////////////////////////////////////////////////////////////////////////
procedure Tdbmodulefrm.getProducts(mstr:TStrings);
var
  list_tmp:Tstringlist;
begin
  list_tmp:=Tstringlist.Create;
  list_tmp.Clear;
  with dbtmpqry do begin
    active :=False;
    sql.Clear;
    sql.Add('Select DISTINCT PRODUCTID from RECORD');
    execsql;
    active:=True;
    first;
    while not eof do begin
      list_tmp.Add(FieldByName('PRODUCTID').AsString);
      next;
    end;
    Active:=False;
  end;
  mstr.Assign(list_tmp);
  list_tmp.Free;
end;
///////////////////////////////////////////////////////////////////////////
procedure Tdbmodulefrm.getScaleID(mstr:TStrings);
var
  list_tmp:Tstringlist;
begin
  list_tmp:=Tstringlist.Create;
  list_tmp.Clear;
  with dbtmpqry do begin
    active :=False;
    sql.Clear;
    sql.Add('Select DISTINCT SCALEID from RECORD');
    execsql;
    active:=True;
    first;
    while not eof do begin
      list_tmp.Add(FieldByName('SCALEID').AsString);
      next;
    end;
    Active:=False;
  end;
  mstr.Assign(list_tmp);
  list_tmp.Free;
end;


procedure  Tdbmodulefrm.getcontractorlist(mstr:TStrings);
var
  list_tmp:Tstringlist;
begin
  list_tmp:=Tstringlist.Create;
  list_tmp.Clear;
  with dbtmpqry do begin
    active :=False;
    sql.Clear;
    sql.Add('select * from contractor order by urfid');
    execsql;
    active:=True;
    first;
    while not eof do begin
      list_tmp.Add(Format('%-4d%s',[FieldByName('ID').asinteger,
                                    FieldByName('Name').AsString]));
      next;
    end;
    Active:=False;
  end;
  mstr.Assign(list_tmp);
  list_tmp.Free;  
end;

function Tdbmodulefrm.usrlogin(const usr_name,usr_pswd:string;var usr_pwr:string):integer;
begin
  with dbquery do begin
    Active:=False;
    SQL.Clear;
    SQL.Add(Format('SELECT * FROM SYS_USER WHERE [NAME]=''%s''',[usr_name]));
    ExecSQL;
    Active:=True;
    if RecordCount<1 then begin
      result:=-1;
    end else begin
      if usr_pswd<>FieldByName('PASWD').AsString then begin
        result:=0;
      end else begin
        result:=1;
        usr_pwr:=fieldbyname('POWER').AsString;
      end;
    end;
    Active:=False;
  end;
end;

procedure Tdbmodulefrm.connectdb(const dbfile:string;const paswd:string='');
begin
  DBCNCT.ConnectionString:=Format(DBCNCTSTR,[dbfile,paswd]);
end;

procedure Tdbmodulefrm.unconnectdb;
begin
  dbquery.Active:=False;
  DBCNCT.ConnectionString:='';
end;

procedure Tdbmodulefrm.DataModuleCreate(Sender: TObject);
begin
  lngfp:=ExtractFilePath(paramstr(0))+'berry.lng';
  lngf:=Tinifile.Create(lngfp);
  inifp:=ExtractFilePath(paramstr(0))+'berry.dll';
  inif:=Tinifile.Create(inifp);
  iniSystemParma;
  connectdb(dbsource,dbpaswd);
end;


procedure Tdbmodulefrm.DataModuleDestroy(Sender: TObject);
begin
  unconnectdb;
end;

end.
